package wiki.mdzz.learn_android.controllers

import android.annotation.SuppressLint
import android.os.Bundle
import android.util.Log
import androidx.core.content.contentValuesOf
import androidx.core.database.getDoubleOrNull
import androidx.core.database.getIntOrNull
import androidx.core.database.getStringOrNull
import wiki.mdzz.learn_android.databinding.ActivityFifthBinding
import wiki.mdzz.learn_android.utils.BaseActivity
import wiki.mdzz.learn_android.utils.DatabaseHelper

class FifthActivity : BaseActivity() {
    private lateinit var binding: ActivityFifthBinding

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        binding = ActivityFifthBinding.inflate(layoutInflater)
        setContentView(binding.root)
        val helper = DatabaseHelper(this, "book_store.db", 1)
        binding.createDatabase.setOnClickListener { helper.writableDatabase }
        binding.insertData.setOnClickListener {
            val db = helper.writableDatabase
            val values =
                contentValuesOf(
                    "name" to "",
                    "name" to "The Da Vinci Code",
                    "author" to "Dan Brown",
                    "pages" to 510,
                    "price" to 16.96
                )
            db.insert("book", null, values)
            db.execSQL(
                "insert into book (name, author, pages, price) values(?, ?, ?, ?)",
                arrayOf("The Lost Symbol", "Dan Brown", 510, 19.95)
            )
        }

        binding.updateData.setOnClickListener {
            val db = helper.writableDatabase
            val values = contentValuesOf("price" to 10.99)
            db.update("book", values, "name = ?", arrayOf("The Da Vinci Code"))
            db.execSQL(
                "update book set pages = ? where name = ?",
                arrayOf("400", "The Da Vinci Code")
            )

            val values1 = contentValuesOf("pages" to 400)
            db.update("category", values1, "name = ?", arrayOf("book"))
        }

        binding.queryData.setOnClickListener {
            val tag = "query data"
            val db = helper.readableDatabase

            val categoryCursor = db.query("category", null, null, null, null, null, null)
            if (categoryCursor.moveToFirst()) {
                do {
                    @SuppressLint("Range")
                    val name = categoryCursor.getStringOrNull(categoryCursor.getColumnIndex("name"))
                        ?: ("")

                    @SuppressLint("Range")
                    val code =
                        categoryCursor.getIntOrNull(categoryCursor.getColumnIndex("code")) ?: 0
                    Log.d(tag, "category name is $name, code is $code")
                } while (categoryCursor.moveToNext())
            }

            val cursor = db.rawQuery("select * from book", null)
            if (cursor.moveToFirst()) {
                do {
                    @SuppressLint("Range")
                    val name = cursor.getStringOrNull(cursor.getColumnIndex("name")) ?: ("")

                    @SuppressLint("Range")
                    val author = cursor.getStringOrNull(cursor.getColumnIndex("author")) ?: ""

                    @SuppressLint("Range")
                    val pages = cursor.getIntOrNull(cursor.getColumnIndex("pages")) ?: 0

                    @SuppressLint("Range")
                    val price = cursor.getDoubleOrNull(cursor.getColumnIndex("price")) ?: 0.0

                    Log.d(
                        tag,
                        "book name is $name, author is $author, pages is $pages, price is $price"
                    )
                } while (cursor.moveToNext())
            }
            categoryCursor.close()
            cursor.close()
        }

        binding.deleteData.setOnClickListener {
            val db = helper.writableDatabase
            //            db.delete("book", "pages > ?", arrayOf("500"))
            db.execSQL("delete from book where pages > ?", arrayOf("400"))
        }

        binding.replaceData.setOnClickListener {
            val db = helper.writableDatabase
            db.beginTransaction()
            try {
                db.execSQL("delete from book")
                val values =
                    contentValuesOf(
                        "name" to "Game of Thrones",
                        "author" to "George Martin",
                        "pages" to 720,
                        "price" to 20.85
                    )
                db.insert("book", null, values)
                db.setTransactionSuccessful()
            } catch (e: Exception) {
                e.printStackTrace()
            } finally {
                db.endTransaction()
            }
        }
    }
}
